<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="au20941">ALTER RESOURCE QUEUE</title>
  <body>
    <p id="sql_command_desc">Changes the limits of a resource queue.</p>
    <section id="section2">
      <title>Synopsis</title>
      <codeblock id="sql_command_synopsis">ALTER RESOURCE QUEUE <varname>name</varname> WITH ( <varname>queue_attribute</varname>=<varname>value</varname> [, ... ] ) </codeblock>
      <p>where <varname>queue_attribute</varname> is:</p>
      <codeblock>   ACTIVE_STATEMENTS=<varname>integer</varname>
   MEMORY_LIMIT='<varname>memory_units</varname>'
   MAX_COST=<varname>float</varname>
   COST_OVERCOMMIT={TRUE|FALSE}
   MIN_COST=<varname>float</varname>
   PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}</codeblock>
      <p/>
      <codeblock>ALTER RESOURCE QUEUE <varname>name</varname> WITHOUT ( <varname>queue_attribute</varname> [, ... ] )</codeblock>
      <p>where <varname>queue_attribute</varname> is:</p>
      <codeblock>   ACTIVE_STATEMENTS
   MEMORY_LIMIT
   MAX_COST
   COST_OVERCOMMIT
   MIN_COST</codeblock>
      <note>A resource queue must have either an <codeph>ACTIVE_STATEMENTS</codeph> or a
          <codeph>MAX_COST</codeph> value. Do not remove both these
          <codeph>queue_attributes</codeph> from a resource queue. </note>
    </section>
    <section id="section3">
      <title>Description</title>
      <p><codeph>ALTER RESOURCE QUEUE</codeph> changes the limits of a resource queue. Only a
        superuser can alter a resource queue. A resource queue must have either an
          <codeph>ACTIVE_STATEMENTS</codeph> or a <codeph>MAX_COST</codeph> value (or it can have
        both). You can also set or reset priority for a resource queue to control the relative share
        of available CPU resources used by queries associated with the queue, or memory limit of a
        resource queue to control the amount of memory that all queries submitted through the queue
        can consume on a segment host.</p>
      <p><codeph>ALTER RESOURCE QUEUE WITHOUT</codeph> removes the specified limits on a resource
        that were previously set. A resource queue must have either an
          <codeph>ACTIVE_STATEMENTS</codeph> or a <codeph>MAX_COST</codeph> value. Do not remove
        both these <codeph>queue_attributes</codeph> from a resource queue.</p>
    </section>
    <section id="section4">
      <title>Parameters</title>
      <parml>
        <plentry>
          <pt><varname>name</varname></pt>
          <pd>The name of the resource queue whose limits are to be altered. </pd>
        </plentry>
        <plentry>
          <pt>ACTIVE_STATEMENTS <varname>integer</varname></pt>
          <pd>The number of active statements submitted from users in this resource queue allowed on
            the system at any one time. The value for <codeph>ACTIVE_STATEMENTS</codeph> should be
            an integer greater than 0. To reset <codeph>ACTIVE_STATEMENTS</codeph> to have no limit,
            enter a value of <codeph>-1</codeph>.</pd>
        </plentry>
        <plentry>
          <pt>MEMORY_LIMIT '<varname>memory_units</varname>'</pt>
          <pd>Sets the total memory quota for all statements submitted from users in this resource
            queue. Memory units can be specified in kB, MB or GB. The minimum memory quota for a
            resource queue is 10MB. There is no maximum; however the upper boundary at query
            execution time is limited by the physical memory of a segment host. The default value is
            no limit (<codeph>-1</codeph>).</pd>
        </plentry>
        <plentry>
          <pt>MAX_COST <varname>float</varname></pt>
          <pd>The total query optimizer cost of statements submitted from users in this resource
            queue allowed on the system at any one time. The value for <codeph>MAX_COST</codeph> is
            specified as a floating point number (for example 100.0) or can also be specified as an
            exponent (for example 1e+2). To reset <codeph>MAX_COST</codeph> to have no limit, enter
            a value of <codeph>-1.0</codeph>.</pd>
        </plentry>
        <plentry>
          <pt>COST_OVERCOMMIT <varname>boolean</varname></pt>
          <pd>If a resource queue is limited based on query cost, then the administrator can allow
            cost overcommit (<codeph>COST_OVERCOMMIT=TRUE</codeph>, the default). This means that a
            query that exceeds the allowed cost threshold will be allowed to run but only when the
            system is idle. If <codeph>COST_OVERCOMMIT=FALSE</codeph> is specified, queries that
            exceed the cost limit will always be rejected and never allowed to run.</pd>
        </plentry>
        <plentry>
          <pt>MIN_COST <varname>float</varname></pt>
          <pd>Queries with a cost under this limit will not be queued and run immediately. Cost is
            measured in units of disk page fetches; 1.0 equals one sequential disk page read. The
            value for <codeph>MIN_COST</codeph> is specified as a floating point number (for example
            100.0) or can also be specified as an exponent (for example 1e+2). To reset
              <codeph>MIN_COST</codeph> to have no limit, enter a value of
            <codeph>-1.0</codeph>.</pd>
        </plentry>
        <plentry>
          <pt>PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}</pt>
          <pd>Sets the priority of queries associated with a resource queue. Queries or statements
            in queues with higher priority levels will receive a larger share of available CPU
            resources in case of contention. Queries in low-priority queues may be delayed while
            higher priority queries are run.</pd>
        </plentry>
      </parml>
    </section>
    <section id="section5">
      <title>Notes</title>
        <p>GPORCA and the Postgres planner utilize different
           query costing models and may compute different costs for the same query.
           The Greenplum Database resource queue resource management scheme neither
           differentiates nor aligns costs between GPORCA and the Postgres Planner; it
           uses the literal cost value returned from the optimizer to throttle
           queries.</p>
        <p>When resource queue-based resource management is active, use the
           <codeph>MEMORY_LIMIT</codeph> and <codeph>ACTIVE_STATEMENTS</codeph> limits for
           resource queues rather than configuring cost-based limits. Even when using GPORCA,
           Greenplum Database may fall back to using the Postgres Planner for certain
           queries, so using cost-based limits can lead to unexpected results.</p>
    </section>
    <section id="section6">
      <title>Examples</title>
      <p>Change the active query limit for a resource queue: </p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20);</codeblock>
      <p>Change the memory limit for a resource queue: </p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITH (MEMORY_LIMIT='2GB');</codeblock>
      <p>Reset the maximum and minimum query cost limit for a resource queue to no limit: </p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=-1.0, 
  MIN_COST= -1.0);</codeblock>
      <p>Reset the query cost limit for a resource queue to 3<sup>10</sup> (or 30000000000.0) and do
        not allow overcommit:</p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=3e+10, 
  COST_OVERCOMMIT=FALSE);</codeblock>
      <p>Reset the priority of queries associated with a resource queue to the minimum level:</p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITH (PRIORITY=MIN);</codeblock>
      <p>Remove the <codeph>MAX_COST</codeph> and <codeph>MEMORY_LIMIT</codeph> limits from a
        resource queue:</p>
      <codeblock>ALTER RESOURCE QUEUE myqueue WITHOUT (MAX_COST, MEMORY_LIMIT);</codeblock>
    </section>
    <section id="section7">
      <title>Compatibility</title>
      <p>The <codeph>ALTER RESOURCE QUEUE</codeph> statement is a Greenplum Database extension. This
        command does not exist in standard PostgreSQL.</p>
    </section>
    <section id="section8">
      <title>See Also</title>
      <p><codeph><xref href="./CREATE_RESOURCE_QUEUE.xml#topic1" type="topic" format="dita"
          /></codeph>, <codeph><xref href="./DROP_RESOURCE_QUEUE.xml#topic1" type="topic"
            format="dita"/></codeph>, <codeph><xref href="./CREATE_ROLE.xml#topic1" type="topic"
            format="dita"/></codeph>, <codeph><xref href="./ALTER_ROLE.xml#topic1" type="topic"
            format="dita"/></codeph></p>
    </section>
  </body>
</topic>
